package com.apes.fn.business.insurance.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

/**
 * @ClassName InsuranceSaleTaskDao
 * @Author wsj
 * @Date 2021/5/26 10:54
 * @description 手写sql
 */

@Component
public class InsuranceSaleTaskDao extends ApesDao {
    /**
     * 查询当天车险销售金额
     *
     * @param requestDate
     * @return
     */
    private final String fieldName = "taskDate";
    public JSONArray findInsuranceSaleData (JSONObject param) {
        String sql = "SELECT STATISTIC_DATE,DEPT,SUM(COMMERCIAL_INSURANCE) AS COMMERCIAL_INSURANCE, " +
                "       SUM(COMPULSORY_INSURANCE) AS COMPULSORY_INSURANCE,SUM(SALE_AMOUNT) AS SALE_AMOUNT " +
                "FROM (  SELECT GET_DATE(A.CREATE_DATE)STATISTIC_DATE,A.DEPT,SUM(A.COMMERCIAL_INSURANCE)COMMERCIAL_INSURANCE, " +
                "         SUM(A.COMPULSORY_INSURANCE)COMPULSORY_INSURANCE,SUM(COMPULSORY_INSURANCE + COMMERCIAL_INSURANCE)SALE_AMOUNT  " +
                "  FROM INSURANCE_ORDER A WHERE TRUNC(A.CREATE_DATE) =:taskDate " +
                "  GROUP BY GET_DATE(A.CREATE_DATE),A.DEPT " +
                "  UNION ALL " +
                "  SELECT GET_DATE(A.CREATE_DATE)STATISTIC_DATE,A.DEPT,SUM(A.COMMERCIAL_INSURANCE)*-1 COMMERCIAL_INSURANCE, " +
                "         SUM(A.COMPULSORY_INSURANCE)*-1 COMPULSORY_INSURANCE,SUM(COMPULSORY_INSURANCE + COMMERCIAL_INSURANCE)*-1 SALE_AMOUNT " +
                "  FROM INSURANCE_ORDER A WHERE A.REFUND_DATE IS NOT NULL AND TRUNC(A.REFUND_DATE) =:taskDate" +
                "  GROUP BY GET_DATE(A.CREATE_DATE),A.DEPT)" +
                " GROUP BY STATISTIC_DATE,DEPT ";

        if (StringUtils.isEmpty(param.getString(fieldName))){
            throw new RuntimeException("必传参数不能为空!!!");
        }
        return toJSONArray(find(sql, param));
    }

    /**
     * 查询保险销售任务
     * @param requestDate
     * @return
     */
    public JSONArray findBusinessStrategyList (JSONObject param) {
        String sql = "SELECT A.ID,C.CODE,B.DEPT_ID,B.TARGET_AMOUNT,A.VALID_PERIOD_START,A.VALID_PERIOD_END" +
                " FROM BUSINESS_STRATEGY A,BUSINESS_STRATEGY_ITEM B,SOE_BUSINESS_ATTR_VALUE C " +
                " WHERE A.ID = B.BUSINESS_STRATEGY AND A.STRATEGY_TYPE = C.ID AND C.CODE IN ('A','B')" +
                "   AND A.VALID_PERIOD_START <=:taskDate AND A.VALID_PERIOD_END >=:taskDate AND A.STATE='approve' ";

        if (StringUtils.isEmpty(param.getString(fieldName))){
            throw new RuntimeException("必传参数不能为空!!!");
        }
        return toJSONArray(find(sql, param));
    }

    /**
     * 查询保险销售任务
     * @param requestDate
     * @return
     */
    public JSONArray queryTotalInsuranceSaleAmount (JSONObject param) {
        String sql = "SELECT SUM(A.COMMERCIAL_INSURANCE_AMOUNT)COMMERCIAL_INSURANCE_AMOUNT,\n" +
                "       SUM(A.TODAY_SALE_AMOUNT)SALE_AMOUNT,A.SALE_AMOUNT_TASK,A.COMMERCIAL_INSURANCE_TASK\n" +
                " FROM  INSURANCE_SALE_TASK A WHERE A.TASK_RULES_NUMBER =:TASK_RULES_NUMBER  AND A.DEPT_ID =:DEPT_ID" +
                " GROUP BY A.SALE_AMOUNT_TASK,A.COMMERCIAL_INSURANCE_TASK ";

        return toJSONArray(find(sql, param));
    }
}
